Why the transaction logs grows are so large?
A Shorter Answer:
You probably either have a long-running transaction running (Index maintenance? Big batch delete or update?), or you are in the “default” (more below on what is meant by default) recovery mode of Full and have not taken a log backup (or aren’t taking them frequently enough).
If it is a recovery model issue, the simple answer could be to Switch to Simple recovery mode if you do not need point-in-time recovery and regular log backups. Many people, though, make that their answer without understanding recovery models. Read on to understand why it matters and then decide what you do. You could also just start taking log backups and stay in Full recovery.
There could be other reasons, but these are the most common. This answer begins to dive into the most common two reasons and gives you some background information on the why and how behind the reasons as well as explores some other reasons.
A longer Answer:
What scenarios can cause the log to keep growing? There are many reasons, but usually, these reasons are of the following two patterns: There is a misunderstanding about recovery models, or there are long-running transactions.
A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what restore operations are available. Three recovery models exist simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time. We are not talking about of bulk-logged recovery mode because is not part of the normal University’s architecture.
Recovery in General Concept
- Crash/Restart Recovery
One purpose of the transaction log file is for crash/restart recovery. For the rolling forward and rolling back of work that was either done (rolling forward/redo) before a crash or restart and the work that was started but not finished after a crash or restart (rolling back/undo). The transaction log’s job is to see that a transaction started but never finished (rolled back or crash/restart happened before the transaction was committed). In that situation, It is the log’s job to say, “Hey… this never really finished, let’s roll it back” during recovery. It is also the log’s job to see that you did finish something and that your client application was told it was finished (even if it hadn’t yet hardened to your data file) and say, “Hey… this really happened, let’s roll it forward, let’s make it like the applications think it was” after a restart. Now there is more, but that is the main purpose.
- Point-in-Time Recovery
The other purpose for a transaction log file is to be able to give us the ability to recover to a point in time due to an “oops” in a database or to guarantee a recovery point in the event of a hardware failure involving the data and/or log files of a database. Suppose this transaction log contains the records of transactions that have been started and finished for recovery. In that case, SQL Server can and does then use this information to get a database to where it was before an issue happened. But that isn’t always an available option for us. For that to work, we have to have our database in the right recovery model and take log backups.
Recovery Models
- Simple Recovery Model
With the above introduction, it is easiest to talk about Simple Recovery model first. In this model, you are telling SQL Server: “I am fine with you using your transaction log file for crash and restart recovery…” (You really have no choice there. Look up ACID properties and that should make sense quickly.) “…but once you no longer need it for that crash/restart recovery purpose, go ahead and reuse the log file.”
SQL Server listens to this request in Simple Recovery and it only keeps the information it needs to do crash/restart recovery. Once SQL Server is sure it can recover because data is hardened to the data file (more or less), the data that has been hardened is no longer necessary in the log and is marked for truncation – which means it gets re-used.
- Full Recovery Model
With Full Recovery, you are telling SQL Server that you want to be able to recover to a specific point in time, as long as your log file is available or to a specific point in time that is covered by a log backup. In this case when SQL Server reaches the point where it would be safe to truncate the log file in Simple Recovery Model, it will not do that. Instead It lets the log file continue to grow and will allow it to keep growing, until you take a log backup (or run out of space on your log file drive) under normal circumstances.
Recovery Models Source Info
Example No 1 When your switch from Simple to Full
There are rules and exceptions here. We’ll talk about long-running transactions in depth below.
But one warning to keep in mind for Full Recovery Mode is this: If you just switch into Full Recovery mode but never take an initial Full Backup, SQL Server will not honour your request to be in the Full Recovery model. Your transaction log will continue to operate as it has in Simple until you switch to Full Recovery Model AND take your first Full Backup.
Full Recovery Model without log backups is bad.
That’s the most common reason for uncontrolled log growth. Answer: Being in Full Recovery mode without having any log backups. Real common mistake.
Why is this such a common mistake?
Why does it happen all the time? Because each new database gets its initial recovery model setting by looking at the model database.
The model’s initial recovery model setting is always Full Recovery Model – until and unless someone changes that. So you could say the “default Recovery Model” is Full. Many people are unaware of this and have their databases running in Full Recovery Model with no log backups; therefore, a transaction log file is much larger than necessary. This is why it is important to change defaults when they don’t work for your organisation and its needs)
Full Recovery Model with too few log backups is bad.
You can also get yourself in trouble here by not taking log backups frequently enough. Taking a log backup a day may sound fine, it makes a restore require less restore commands, but keeping in mind the discussion above, that log file will continue to grow and grow until you take log backups. That point should not be a problem with Maint – DatabaseBackup – ALL_DATABASES – LOG job.
How do I find out what log backup frequency I need? Answer that you need respond when you are designing your solution.
You need to consider your log backup frequency with two things in mind:
- Recovery Needs– This should hopefully be first. In the event that the drive housing your transaction log goes bad or you get serious corruption that affects your log backup, how much data can be lost? If that number is no more than 10-15 minutes, then you need to be taking the log backup every 10-15 minutes, end of discussion.
- Log Growth– If your organisation is fine with losing more data because of the ability to recreate that day easily, you may be fine to have a log backup much less frequently than 15 minutes. Maybe your organization is fine with every 4 hours. But you must look at how many transactions you generate in 4 hours. Will allowing the log to keep growing in those four hours make too large of a log file? Will that mean your log backups take too long?
Querying the sys.databases catalog view, you can see information describing why your log file may be waiting on truncate/reuse.
There is a column called log_reuse_wait with a lookup ID of the reason code and a log_reuse_wait_desc column with a description of the wait reason. From the referenced books and online article are the majority of the reasons (the ones you are likely to see and the ones we can explain reasons for. What does means these columns?
select name, log_reuse_wait_desc, log_reuse_wait from sys.databases
- 0 = Nothing
What it sounds like.. Shouldn’t be waiting - 1 = Checkpoint
Waiting for a checkpoint to occur. This should happen and you should be fine – but there are some cases to look for here for later answers or edits. - 2 = Log backup
You are waiting for a log backup to occur. Either you have them scheduled and it will happen soon, or you have the first problem described here and you now know how to fix it - 3 = Active backup or restore
A backup or restore operation is running on the database - 4 = Active transaction
There is an active transaction that needs to complete (either way – ROLLBACK or COMMIT) before the log can be backed up. This is the second reason described in this answer. - 5 = Database mirroring
Either a mirror is getting behind or under some latency in a high performance mirroring situation or mirroring is paused for some reason - 6 = Replication
There can be issues with replication that would cause this – like a log reader agent not running, a database thinking it is marked for replication that no longer is and various other reasons. You can also see this reason and it is perfectly normal because you are looking at just the right time, just as transactions are being consumed by the log reader - 7 = Database snapshot creation
You are creating a database snapshot, you’ll see this if you look at just the right moment as a snapshot is being created - 8 = Log Scan
I have yet to encounter an issue with this running along forever. If you look long enough and frequently enough you can see this happen, but it shouldn’t be a cause of excessive transaction log growth, that I’ve seen. - 9 = An Always On Availability Groups secondary replica is applying transaction log records of this database to a corresponding secondary database. About the clearest description yet..
Some things you don’t want to do:
- Back up the log with TRUNCATE_ONLY option and then SHRINKFILE. For one, this TRUNCATE_ONLY option has been deprecated and is no longer available in current versions of SQL Server. Second, if you are in a FULL recovery model, this will destroy your log chain and require a new, full backup.
- Detach the database, delete the log file, and re-attach. I can’t emphasize how dangerous this can be. Your database may not come back up, it may come up as a suspect, you may have to revert to a backup (if you have one), etc. etc.
- Use the “shrink database” option. DBCC SHRINKDATABASE and the maintenance plan option to do the same are bad ideas, especially if you really only need to resolve a log problem issue. Target the file you want to adjust and adjust it independently, using DBCC SHRINKFILE or ALTER DATABASE … MODIFY FILE (examples above).
- Shrink the log file to 1 MB. This looks tempting because, hey, SQL Server will let me do it in certain scenarios, and look at all the space it frees! Unless your database is read-only (and it is, you should mark it as such using ALTER DATABASE), this will absolutely just lead to many unnecessary growth events, as the log has to accommodate current transactions regardless of the recovery model. What is the point of freeing up that space temporarily just so SQL Server can take it back slowly and painfully?
- Create a second log file. This will provide temporary relief for the drive that has filled your disk, but this is like trying to fix a punctured lung with a band-aid. You should deal with the problematic log file directly instead of just adding another potential problem. Other than redirecting some transaction log activity to a different drive, a second log file really does nothing for you (unlike a second data file), since only one of the files can ever be used at a time. Paul Randal also explains why multiple log files can bite you later.
Credits
Paul S. Randal – https://www.sqlskills.com/blogs/paul/sqlskills-sql101-switching-recovery-models/
https://dba.stackexchange.com/users/1192/paul-white
https://www.brentozar.com/archive/2016/03/my-favorite-system-column-log_reuse_wait_desc/
